<?php

namespace App\Libs\Company\SheetTemplateHandler\Aggregate;

use App\Libs\Company\SheetTemplateHandler\Aggregate\AbstractTemplate;
use App\Libs\Company\SheetTemplateHandler\Manager;
use App\Models\AggregateReport;
use App\Models\SheetTemplate;

class Template222 extends AbstractTemplate
{
    public $dataStartRow = 3;

    protected $monthData = [];

    protected $lastMonthData = [];

    protected $areas = [
        '台湾同胞' => 'cn',
        '香港同胞' => 'cn',
        '澳门同胞' => 'cn',
        '日本' => '亚洲',
        '菲律宾' => '亚洲',
        '新加坡' => '亚洲',
        '泰国' => '亚洲',
        '印尼' => '亚洲',
        '马来西亚' => '亚洲',
        '韩国' => '亚洲',
        '蒙古' => '亚洲',
        '印度' => '亚洲',
        '其他（亚洲）' => '亚洲',
        '美国' => '美洲',
        '加拿大' => '美洲',
        '其它（美洲）' => '美洲',
        '英国' => '欧洲',
        '法国' => '欧洲',
        '德国' => '欧洲',
        '意大利' => '欧洲',
        '瑞士' => '欧洲',
        '瑞典' => '欧洲',
        '荷兰' => '欧洲',
        '俄罗斯' => '欧洲',
        '西班牙' => '欧洲',
        '其它（欧洲）' => '欧洲',
        '澳大利亚' => '大洋洲',
        '新西兰' => '大洋洲',
        '其它（大洋洲）' => '大洋洲',
        '非洲' => '非洲',
        '其他' => '其他',
    ];

    protected $outputAreas = [
        '亚洲' => [
            '日本',
            '菲律宾',
            '新加坡',
            '泰国',
            '印尼',
            '马来西亚',
            '韩国',
            '蒙古',
            '印度',
            '其他（亚洲）',
        ],
        '美洲' => [
            '美国',
            '加拿大',
            '其它（美洲）',
        ],
        '欧洲' => [
            '英国',
            '法国',
            '德国',
            '意大利',
            '瑞士',
            '瑞典',
            '荷兰',
            '俄罗斯',
            '西班牙',
            '其它（欧洲）',
        ],
        '大洋洲' => [
            '澳大利亚',
            '新西兰',
            '其它（大洋洲）',
        ],
    ];

    public function generateAbstract()
    {
        $this->renderHeader();
        $this->output['sheets']['sheet-01']['cellData']['0']['0'] = $this->createTitleCell("日喀则市{$this->task->year}年{$this->task->key}月\r{$this->template->name}");

        $this->monthData = [
            '（二）入境游客' => 0,
        ];
        foreach ($this->task->subTasks as $subTask) {
            $handler = Manager::createReportHandler(SheetTemplate::find($this->task->sheet_template_id), $subTask);
            $data = $handler->getData();
            foreach ($data as $key => $value) {
                if (isset($this->areas[$key])) {
                    $this->monthData[$key] = ($monthData[$key] ?? 0) + (int) $value[2]['v'];
                    $this->monthData['（二）入境游客'] += (int) $value[2]['v'];
                }
            }
        }

        $this->lastMonthData = self::getLastMonthData();

        $this->output['sheets']['sheet-01']['cellData'][2] = [
            ['v' => 1, 's' => 'CENTER'],
            ['v' => '总人数', 's' => 'CENTER', 't' => 1],
            ['f' => '=C4+C5', 't' => 2],
            ['f' => '=D4+D5', 't' => 2],
            ['f' => '=E4+E5', 't' => 2],
            ['f' => '=F4+F5', 't' => 2],
        ];
        $this->output['sheets']['sheet-01']['cellData'][4] = $this->generateRow(3, '（二）入境游客');
        // 同期主要指标表
        $aggregateReport221 = AggregateReport::where([
            'sheet_template_id' => 221,
            'frequency' => $this->task->frequency,
            'year' => $this->task->year,
            'key' => $this->task->key,
        ])
            ->first();
        if ($aggregateReport221) {
            $this->monthData['（一）国内游客'] = $aggregateReport221->data['sheets']['sheet-01']['cellData'][9][2]['v'];

            /**
             * @var \App\Libs\Company\SheetTemplateHandler\Aggregate\Template221 $handler221
             */
            $handler221 = Manager::createAggregateHandler(SheetTemplate::find($aggregateReport221->sheet_template_id), $this->task);
            $handler221->updateOverseas($aggregateReport221, $this->output['sheets']['sheet-01']['cellData'][4][2]['v']);
        } else {
            $this->monthData['（一）国内游客'] = 0;
        }

        $this->output['sheets']['sheet-01']['cellData'][3] = $this->generateRow(2, '（一）国内游客');

        $this->output['sheets']['sheet-01']['cellData'][5] = $this->generateRow(4, '台湾同胞', '1、台湾同胞');
        $this->output['sheets']['sheet-01']['cellData'][6] = $this->generateRow(5, '香港同胞', '2、香港同胞');
        $this->output['sheets']['sheet-01']['cellData'][7] = $this->generateRow(6, '澳门同胞', '3、澳门同胞');
        $this->output['sheets']['sheet-01']['cellData'][8] = [
            ['v' => 7, 's' => 'CENTER'],
            ['v' => '4、外国人', 's' => 'CENTER', 't' => 1],
            ['f' => '=C5-C6-C7-C8', 't' => 2],
            ['f' => '=D5-D6-D7-D8', 't' => 2],
            ['f' => '=E5-E6-E7-E8', 't' => 2],
            ['f' => '=F5-F6-F7-F8', 't' => 2],
        ];

        $sn = 8;
        foreach ($this->outputAreas as $continent => $areas) {
            $currRow = $startRow = $sn + 1;
            foreach ($areas as $area) {
                $this->output['sheets']['sheet-01']['cellData'][$currRow++] = $this->generateRow($sn++, $area);
            }
            $this->output['sheets']['sheet-01']['cellData'][$currRow] = [
                ['v' => $sn++, 's' => 'CENTER'],
                ['v' => $continent . '小计', 's' => 'CENTER', 't' => 1],
                ['f' => "=SUM(C" . ($startRow + 1) . ":C{$currRow})", 't' => 2],
                ['f' => "=SUM(D" . ($startRow + 1) . ":D{$currRow})", 't' => 2],
                ['f' => "=SUM(E" . ($startRow + 1) . ":E{$currRow})", 't' => 2],
                ['f' => "=SUM(F" . ($startRow + 1) . ":F{$currRow})", 't' => 2],
            ];
        }
        $this->output['sheets']['sheet-01']['cellData'][$sn] = $this->generateRow($sn++, '非洲', '非洲小计');
        $this->output['sheets']['sheet-01']['cellData'][$sn] = $this->generateRow($sn++, '其他', '其他小计');

        return $this->output;
    }

    /**
     * @param mixed $sn 序号
     * @param mixed $key 填报表的行头
     * @param mixed $name 如与填报表不同，汇总表的行头
     * @return array
     */
    public function generateRow($sn, $key, $name = null)
    {
        return [
            ['v' => $sn, 's' => 'CENTER', 't' => 2],
            ['v' => $name ?: $key, 's' => 'CENTER', 't' => 1],
            ['v' => $this->monthData[$key], 't' => 2],
            ['v' => $this->monthData[$key] + ($this->lastMonthData[$name ?: $key][3]['v'] ?? 0), 't' => 2],
            ['v' => floor($this->monthData[$key] * 1.1), 't' => 2],
            ['v' => floor(($this->monthData[$key] + ($this->lastMonthData[$name ?: $key][3]['v'] ?? 0)) * 1.1), 't' => 2],
        ];
    }

    public function getLastMonthData()
    {
        if ($this->task->key == 1) {
            return [];
        }

        $lastAggregateReport = AggregateReport::where([
            'sheet_template_id' => $this->template->id,
            'frequency' => $this->task->frequency,
            'year' => $this->task->year,
            'key' => $this->task->key - 1,
        ])
            ->first();
        if (!$lastAggregateReport) {
            return [];
        }
        $data = [];
        while (1) {
            $row = $lastAggregateReport->data['sheets']['sheet-01']['cellData'][$this->dataEndRow++] ?? [];
            if (!isset($row[0]['v']) || !is_numeric($row[0]['v'])) {
                break;
            }
            $data[$row[1]['v']] = $row;
        }
        return $data;
    }

    public function updateDomestic(AggregateReport $aggregateReport, $value)
    {
        $this->lastMonthData = $this->getLastMonthData();
        $this->monthData['（一）国内游客'] = $value;

        $data = $aggregateReport->data;
        $cellData = &$data['sheets']['sheet-01']['cellData'];
        $cellData[3] = $this->generateRow(2, '（一）国内游客');

        $aggregateReport->data = $data;
        return $aggregateReport->save();
    }
}
